package com.iamteer.day17;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;

import org.junit.Test;

import com.iamteer.util.JdbcUtil;

public class CallableStatementEg {
	/**
	 * 调用带有输入参数的存储过程
	 */
	@Test
	public void test1() {
		Connection conn = null;
		CallableStatement stmt = null;
		ResultSet rs = null;
		try {
			//1 获取连接对象
			conn = JdbcUtil.getConnection();
			//2 准备预编译的 sql
			String sql = "CALL pro_findById(?)";
			//3 预编译
			stmt = conn.prepareCall(sql);
			//4 设置参数
			stmt.setInt(1, 2);
			//5 发送 sql，执行 sql，返回结果
			rs = stmt.executeQuery();
			//6 遍历结果
			while (rs.next()) {
				int id = rs.getInt("id");
				String username = rs.getString("username");
				String password = rs.getString("password");
				System.out.println(id + " " + username + " " + password);
			}
		} catch (Exception e) {
			throw new RuntimeException(e);
		} finally {
			JdbcUtil.close(conn, stmt, rs);
		}
	}

	/**
	 * 调用带有输入输出参数的存储过程
	 */
	@Test
	public void test2() {
		Connection conn = null;
		CallableStatement stmt = null;
		ResultSet rs = null;
		try {
			//1 获取连接对象
			conn = JdbcUtil.getConnection();
			//2 准备预编译的 sql
			String sql = "CALL pro_findById2(?,?)";
			//3 预编译
			stmt = conn.prepareCall(sql);
			//4 设置参数
			//输入参数
			stmt.setInt(1, 2);
			//注册输出参数
			stmt.registerOutParameter(2, java.sql.Types.VARCHAR);
			//5 发送 sql，执行 sql，返回结果
			rs = stmt.executeQuery();
			//6 获取返回结果
			String result = stmt.getString(2);
			System.out.println(result);
		} catch (Exception e) {
			throw new RuntimeException(e);
		} finally {
			JdbcUtil.close(conn, stmt, rs);
		}
	}
}
